<?php
/***************************************************************
 *  Copyright notice
 *
 *  (c) 2007-2008 Starbugs (univis2typo3@googlegroups.com)
 *  All rights reserved
 *
 *  This script is part of the TYPO3 project. The TYPO3 project is
 *  free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  The GNU General Public License can be found at
 *  http://www.gnu.org/copyleft/gpl.html.
 *  A copy is found in the textfile GPL.txt and important notices to the license
 *  from the author is found in LICENSE.txt distributed with these scripts.
 *
 *
 *  This script is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  This copyright notice MUST APPEAR in all copies of the script!
 ***************************************************************/
/**
 * Module: Web>UnivIS2Typo3
 *
 * Description:
 *
 * Notice: 
 *
 *  
 * @author	Starbugs <univis2typo3@googlegroups.com>
 */

class UnivisTableManager
{ 
    function create_db_schema()
    {
	$sql_query = array();
	$sql_query[]='SET GLOBAL group_concat_max_len = 16384';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_person (
		`department_id` INTEGER,	
		`key` VARCHAR(255),
		`type` VARCHAR(50),
		`group` VARCHAR(50),
		atitle VARCHAR(50),
		firstname VARCHAR(100),
		`from` VARCHAR(255),
		`id` VARCHAR(255),
		lastname VARCHAR(100),
		lehr VARCHAR(255),
		lehraufg VARCHAR(255),
		lehrtyp VARCHAR(255),
		pgroup VARCHAR(255),
		shortname VARCHAR(255),
		`title` VARCHAR(255),
		`univis_key` VARCHAR(255),
		`until` VARCHAR(255),
		visible VARCHAR(255),
		work VARCHAR(255),
		zweitmgl VARCHAR(255),
		alumni VARCHAR(255),
		chef VARCHAR(255),
		founder VARCHAR(255),
		name VARCHAR(255),
		current ENUM ( "0", "1" ) DEFAULT "0",
		gender SET ( "female", "male" ),

		PRIMARY KEY(`key`)
		    )';
	//    projects := ( HUMAINE, IMPRS, PF-STAR, SFB-539, SFB-603, SVcheck, SmartWeb )* -> u_person_research,
	// type := acad, admin, azubi, externalPhD, guest, info, secretary, trainee, webmaster

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_person_location (
		`department_id` INTEGER,	
		person_key VARCHAR(255) references u_person, 
		orderindex INTEGER,
		email VARCHAR(255),
		fax VARCHAR(255),
		mobile VARCHAR(255),
		office VARCHAR(255),
		ort VARCHAR(255),
		street VARCHAR(255),
		tel VARCHAR(255),
		url VARCHAR(255),
		PRIMARY KEY (person_key, orderindex)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_person_officehour (
		`department_id` INTEGER,	
		person_key VARCHAR(255) references u_person, 
		orderindex INTEGER,
		endtime VARCHAR(255),
		office VARCHAR(255),
		`repeat` VARCHAR(255),
		starttime VARCHAR(255),
		`comment` VARCHAR(255),
		PRIMARY KEY (person_key, orderindex)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_research (
		`department_id` INTEGER,	
		`key` VARCHAR(255) PRIMARY KEY,
		contact VARCHAR(255) references u_person, 
		description TEXT,
		description_en TEXT,
		enddate VARCHAR(255),
		orgname VARCHAR(255),
		keywords VARCHAR(255),
		keywords_en VARCHAR(255),
		startdate VARCHAR(255),
		title VARCHAR(255),
		title_en VARCHAR(255),
		url VARCHAR(255),
		url_en VARCHAR(255)

		)';
	// TODO    
	//    promoters , -> u_research_promotions ?
	//    coworkers , -> u_research_coworkers
	//    directors , -> u_research_directors
	//    externals , -> u_research_externals
	//    publics , -> u_research_publics

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_research_promoters (
		`department_id` INTEGER,	
		project_key VARCHAR(255) references u_research, 
		name VARCHAR(255),
		url VARCHAR(255),
		PRIMARY KEY (project_key, name)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_research_externals (
		`department_id` INTEGER,	
		project_key VARCHAR(255) references u_research, 
		name VARCHAR(255),
		url VARCHAR(255),
		PRIMARY KEY (project_key, name)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_research_publics (
		`department_id` INTEGER,	
		project_key VARCHAR(255) references u_research, 
		`public` VARCHAR(255),
		PRIMARY KEY (project_key, `public`)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_research_directors (
		`department_id` INTEGER,	
		project_key VARCHAR(255) references u_research, 
		director VARCHAR(255) references u_person, 
		PRIMARY KEY (project_key, director)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_research_coworkers (
		`department_id` INTEGER,	
		project_key VARCHAR(255) references u_research, 
		coworker VARCHAR(255) references u_person, 
		PRIMARY KEY (project_key, coworker)
		)';

	// $sql_query[]='CREATE TABLE IF NOT EXISTS u_person_research (
	//     person_key VARCHAR(255) references u_person, 
	//     project_key VARCHAR(255) references u_research, 
	//     PRIMARY KEY (person_key, project_key)
	// )';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_title (
		`department_id` INTEGER,	
		`key` VARCHAR(255) PRIMARY KEY,
		parent_title_key VARCHAR(255) references u_title,
		title VARCHAR(255),
		title_en VARCHAR(255),
		`text` TEXT,
		text_en TEXT,
		ordernr VARCHAR(255),
		parent_title VARCHAR(255)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_room (
		`department_id` INTEGER,	
		`key` VARCHAR(255) PRIMARY KEY,
		address VARCHAR(255),
		description VARCHAR(255),
		anst VARCHAR(255),
		audio VARCHAR(255),
		beam VARCHAR(255),
		buildno VARCHAR(255),
		dark VARCHAR(255),
		dia VARCHAR(255),
		fest VARCHAR(255),
		`id` VARCHAR(255),
		inet VARCHAR(255),
		lose VARCHAR(255),
		name VARCHAR(255),
		ohead VARCHAR(255),
		roomno VARCHAR(255),
		short VARCHAR(255),
		size VARCHAR(255),
		tafel VARCHAR(255),
		tel VARCHAR(255),
		url VARCHAR(255),
		vcr VARCHAR(255)
		    )';
	//    contacts , -> u_rooms_contacts

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_room_contacts (
		`department_id` INTEGER,	
		room_key VARCHAR(255) references u_room,
		contact VARCHAR(255) references u_person, 
		PRIMARY KEY (contact, room_key)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_lecture (
		`department_id` INTEGER,	
		`key` VARCHAR(255),
		semester VARCHAR(6),
		classification VARCHAR(255) references u_title,
		parent_lv VARCHAR(255) references u_lecture,
		room VARCHAR(255) references u_room,
		organizational VARCHAR(255),
		summary TEXT,
		ects_summary TEXT,
		name VARCHAR(255),
		ects_name VARCHAR(255),

		beginners VARCHAR(255),
		benschein VARCHAR(255),
		`comment` VARCHAR(255),
		ects VARCHAR(255),
		ects_cred VARCHAR(255),
		ects_literature VARCHAR(255),
		ects_organizational VARCHAR(255),
		evaluation VARCHAR(255),
		fachstud VARCHAR(255),
		fruehstud VARCHAR(255),
		gast VARCHAR(255),
		`id` VARCHAR(255),
		internat VARCHAR(255),
		keywords VARCHAR(255),
		literature VARCHAR(255),
		ordernr VARCHAR(255),
		regsystem VARCHAR(255),
		schein VARCHAR(255),
		short VARCHAR(255),
		startdate VARCHAR(255),
		sws VARCHAR(255),
		time_description VARCHAR(255),
		turnout VARCHAR(255),
		`type` VARCHAR(255),
		url_description VARCHAR(255),
		PRIMARY KEY (`key`, `semester`)
		    )';
	// TODO
	//    courses , -> u_lecture_courses
	//    dozs , -> u_lecture_dozs
	//    literature , -> u_lecture_literature
	//    studs , -> u_lecture_stud
	//    terms , -> u_lecture_term
	//    parent-lv , -> parent_lecture ?

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_lecture_courses (
		`department_id` INTEGER,	
		lecture_key VARCHAR(255) references u_lecture,
		course VARCHAR(255) references u_lecture,
		semester VARCHAR(6),
		orderindex INTEGER,
		PRIMARY KEY (lecture_key, semester, course)
		)';

	// $sql_query[]='CREATE TABLE IF NOT EXISTS u_lecture_literature (
	//     lecture_key VARCHAR(255) references u_lecture,
	//     orderindex INTEGER,
	//     name VARCHAR(255),
	//     PRIMARY KEY (lecture_key, orderindex)
	// )';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_lecture_stud (
		`department_id` INTEGER,	
		lecture_key VARCHAR(255) references u_lecture,
		pflicht VARCHAR(50),
		richt VARCHAR(50),
		sem VARCHAR(30),
		semester VARCHAR(6),
		orderindex INTEGER,
		PRIMARY KEY (lecture_key, richt, semester, orderindex)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_lecture_term (
		`department_id` INTEGER,	
		lecture_key VARCHAR(255) references u_lecture,
		orderindex INTEGER,
		room VARCHAR(255) references u_room,
		starttime VARCHAR(50),
		endtime VARCHAR(50),
		exclude VARCHAR(50),
		semester VARCHAR(6),
		`repeat` VARCHAR(10),
		PRIMARY KEY (lecture_key, `repeat`, starttime, semester, orderindex)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_lecture_dozs (
		`department_id` INTEGER,	
		lecture_key VARCHAR(255) references u_lecture,
		doz VARCHAR(255) references u_person, 
		semester VARCHAR(6),
		PRIMARY KEY (lecture_key, doz, semester)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_thesis ( 
		`department_id` INTEGER,	
		`key` VARCHAR(255) PRIMARY KEY,
		title VARCHAR(255),
		notice VARCHAR(255),
		topic VARCHAR(255),
		finishdate VARCHAR(255),
		finishyear VARCHAR(255),
		firstname VARCHAR(255),
		keywords VARCHAR(255),
		lastname VARCHAR(255),
		prerequisit VARCHAR(255),
		public VARCHAR(255),
		registerdate VARCHAR(255),
		reservedate VARCHAR(255),
		short VARCHAR(255),
		status VARCHAR(255),
		`type` VARCHAR(255),
		url VARCHAR(255),
		visible VARCHAR(255)
		)';
	// TODO
	//    advisors VARCHAR(255), -> u_thesis_advisors

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_thesis_advisors ( 
		`department_id` INTEGER,	
		thesis_key VARCHAR(255) references u_theses,
		advisor VARCHAR(255) references u_person,
		PRIMARY KEY (thesis_key, advisor)
		)';


	$sql_query[]='CREATE TABLE IF NOT EXISTS u_pub ( 
		`department_id` INTEGER,	
		`key` VARCHAR(255) PRIMARY KEY,
		pubtitle VARCHAR(255),
		address VARCHAR(255),
		booktitle VARCHAR(255),
		conf_url VARCHAR(255),
		conference VARCHAR(255),
		edition VARCHAR(255),
		hstype VARCHAR(255),
		hsyear VARCHAR(255),
		`id` VARCHAR(255),
		isbn VARCHAR(255),
		issn VARCHAR(255),
		journal VARCHAR(255),
		keywords VARCHAR(255),
		`number` VARCHAR(255),
		pages VARCHAR(255),
		plocation VARCHAR(255),
		publisher VARCHAR(255),
		puburl VARCHAR(255),
		school VARCHAR(255),
		series VARCHAR(255),
		servolume VARCHAR(255),
		`type` VARCHAR(255),
		volume VARCHAR(255),
		year VARCHAR(255)
		    )';
	// TODO
	//    authors , -> u_pub_authors
	//    editors , -> u_pub_editors

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_pub_editors ( 
		`department_id` INTEGER,	
		pub_key VARCHAR(255) references u_pub,
		pkey VARCHAR(255) references u_person,
		orderindex INTEGER,
		PRIMARY KEY (pub_key, pkey),
		UNIQUE INDEX (pub_key, orderindex)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_pub_authors ( 
		`department_id` INTEGER,	
		pub_key VARCHAR(255) references u_pub,
		pkey VARCHAR(255) references u_person,
		orderindex INTEGER,
		PRIMARY KEY (pub_key, pkey),
		UNIQUE INDEX (pub_key, orderindex)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_job ( 
		`department_id` INTEGER,	
		`parent_key` VARCHAR(255),
		`description` VARCHAR(255) PRIMARY KEY,
		`description_en` VARCHAR(255),
		`flags` VARCHAR(255)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_person_jobs (
		`department_id` INTEGER,	
		per VARCHAR(255) references u_person,
		job_key VARCHAR(255) references u_job,
		PRIMARY KEY (per, job_key)
		)';

	$sql_query[]='CREATE TABLE IF NOT EXISTS u_org (
		`department_id` INTEGER,	
		`key` VARCHAR(255) PRIMARY KEY,
		b_lehre VARCHAR(255),
		b_orgdesc VARCHAR(255),
		b_rescoop VARCHAR(255),
		b_resequip VARCHAR(255),
		b_resfoc VARCHAR(255),
		`email` VARCHAR(255),
		fax VARCHAR(255),
		`id` VARCHAR(255),
		`name`VARCHAR(255),
		name_en VARCHAR(255),
		ordernr VARCHAR(255),
		orgdesc VARCHAR(255),
		orgdesc_en VARCHAR(255),
		orgnr VARCHAR(255),
		ort VARCHAR(255),
		pubser VARCHAR(255),
		pubser_en VARCHAR(255),
		resconf VARCHAR(255),
		resconf_en VARCHAR(255),
		rescoop VARCHAR(255),
		rescoop_en VARCHAR(255),
		resequip VARCHAR(255),
		resequip_en VARCHAR(255),
		resfoc VARCHAR(255),
		resfoc_en VARCHAR(255),
		street VARCHAR(255),
		tel VARCHAR(255),
		url VARCHAR(255)
		    )';

	foreach ($sql_query as $q)
	{
	    $GLOBALS['TYPO3_DB']->sql_query($q);
	}

    } 

    function create_db_static_tables()
    {
    } 

    function delete_db_data()
    {
	$sql_query = array();
	$sql_query[]='delete from u_job';
	$sql_query[]='delete from u_person'; 
	$sql_query[]='delete from u_person_jobs';
	$sql_query[]='delete from u_person_location'; 
	$sql_query[]='delete from u_person_officehour';
	$sql_query[]='delete from u_title'; 
	$sql_query[]='delete from u_research';
	$sql_query[]='delete from u_research_coworkers';
	$sql_query[]='delete from u_research_directors';
	$sql_query[]='delete from u_research_externals';
	$sql_query[]='delete from u_research_promoters';
	$sql_query[]='delete from u_research_publics';
	$sql_query[]='delete from u_room'; 
	$sql_query[]='delete from u_room_contacts';
	$sql_query[]='delete from u_lecture'; 
	$sql_query[]='delete from u_lecture_courses';
	$sql_query[]='delete from u_lecture_stud';
	$sql_query[]='delete from u_lecture_term';
	$sql_query[]='delete from u_lecture_dozs';
	$sql_query[]='delete from u_thesis'; 
	$sql_query[]='delete from u_thesis_advisors'; 
	$sql_query[]='delete from u_pub'; 
	$sql_query[]='delete from u_pub_editors'; 
	$sql_query[]='delete from u_pub_authors';
	$sql_query[]='delete from u_org';

	foreach ($sql_query as $q)
	{
	    $GLOBALS['TYPO3_DB']->sql_query($q);
	}
    } 

    function drop_univis_tables()
    {
	$sql_query = array();
	$sql_query[]='drop table u_job';
	$sql_query[]='drop table u_person'; 
	$sql_query[]='drop table u_person_jobs';
	$sql_query[]='drop table u_person_location'; 
	$sql_query[]='drop table u_person_officehour';
	$sql_query[]='drop table u_title'; 
	$sql_query[]='drop table u_research';
	$sql_query[]='drop table u_research_coworkers';
	$sql_query[]='drop table u_research_directors';
	$sql_query[]='drop table u_research_externals';
	$sql_query[]='drop table u_research_promoters';
	$sql_query[]='drop table u_research_publics';
	$sql_query[]='drop table u_room'; 
	$sql_query[]='drop table u_room_contacts';
	$sql_query[]='drop table u_lecture'; 
	$sql_query[]='drop table u_lecture_courses';
	$sql_query[]='drop table u_lecture_stud';
	$sql_query[]='drop table u_lecture_term';
	$sql_query[]='drop table u_lecture_dozs';
	$sql_query[]='drop table u_thesis'; 
	$sql_query[]='drop table u_thesis_advisors'; 
	$sql_query[]='drop table u_pub'; 
	$sql_query[]='drop table u_pub_editors'; 
	$sql_query[]='drop table u_pub_authors';
	$sql_query[]='drop table u_org';

	foreach ($sql_query as $q)
	{
	    $GLOBALS['TYPO3_DB']->sql_query($q);
	}
	echo mysql_error();
    } 

}
?>
